# -*- coding: utf-8 -*-
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
#作者：cacho_37967865
#博客：https://blog.csdn.net/sinat_37967865
#文件：stock_analyse.py
#日期：2020-10-16
#备注：股票分析
CREATE TABLE `stock_7days` (
  `trade_date` varchar(32) NOT NULL COMMENT '开始日期',
  `stock_no` varchar(10) NOT NULL,
  `stock_name` varchar(10) DEFAULT NULL,
  `price_max` float DEFAULT 0.0 COMMENT '第一天最高价',
  `type` varchar(32) NOT NULL COMMENT '突破类型：涨停温和放量1、涨停1.5倍放量2',
  `target_date` varchar(32) NOT NULL COMMENT '第7预计交易日期',
  `target_price` float DEFAULT 0.0 COMMENT '第7预计交易日最新价',
  `target_chg` float DEFAULT 0.0 COMMENT '第7预计交易日涨跌幅',
	`target_is` varchar(32) NOT NULL COMMENT '是否达到买入条件（最新收盘价大于涨停收盘价的-10%）：是、否',
	`result_chg` varchar(32) NOT NULL COMMENT '第7/8/9预计交易日期表现',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`trade_date`,`stock_no`,`target_date`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='冲哥横盘震荡裴波周期股票统计信息';
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
from pacong.stock.stock_config import db,cursor
from pycacho.cachobase.chinese_workday import get_trade_day

def delete_mysql():
    sql = 'delete from stock_7days where trade_date=%s;' % (get_trade_day(1,'s'))
    cursor.execute(sql)
    db.commit()

# N-7天前涨停，N-6天跳空放量50%以上，N日不破N-7日收盘价
def top_up(start_day,change,type):
    sql = 'select trade_date as 统计日期,stock_No as 股票代码,stock_name as 股票名称,price_max as 最高价,price_chg as 涨跌幅,turnover,price_min from stock_tradeinfo_total where trade_date="%s" and SUBSTR(stock_no,1,3)="300" and price_chg>"%s" order by stock_no ;' %(start_day,change)
    cursor.execute(sql)
    results = cursor.fetchall()
    stocks = []
    for stock in results:
        row =[]
        for info in stock:
            row.append(info)
        stocks.append(row)
    print(start_day,type,stocks)
    return stocks

# 今天最低价大于昨天涨停价
def top_next(start_day,stock_no,price_max,turnover):
    sql = 'select trade_date as 统计日期,stock_No as 股票代码,stock_name as 股票名称,price_min as 最低价,price_chg as 涨跌幅,turnover from stock_tradeinfo_total where trade_date="%s" and stock_no="%s" and price_min>"%s" and turnover>"%s" order by stock_no ;' %(start_day,stock_no,price_max,turnover)
    cursor.execute(sql)
    #print(sql)
    results = cursor.fetchone()
    if results:
        print("满足冲哥横盘震荡裴波周期股票信息：",results)
        return results

# 前一天最高价大于今天最低价，并且前一天不涨停
def top_last(start_day,stock_no,price_new,turnover):
    sql = 'select trade_date as 统计日期,stock_No as 股票代码,stock_name as 股票名称,price_max as 最高价,price_chg as 涨跌幅,turnover_rate from stock_tradeinfo_total where trade_date="%s" and stock_no="%s" and price_max<"%s" and turnover<"%s" and price_chg<15 order by stock_no ;' %(start_day,stock_no,price_new,turnover)
    cursor.execute(sql)
    #print(sql)
    results = cursor.fetchone()
    if results:
        print("满足冲哥横盘震荡裴波周期股票信息：",results)
        return results

def data_mysql(trade_date, stock_no, stock_name,price_max,type,target_date):
    sql = "insert into stock_7days (trade_date,stock_no,stock_name,price_max,type,target_date,target_is,result_chg,create_time) values ('%s','%s','%s','%s','%s','%s','','',now());" % (trade_date, stock_no, stock_name,price_max,type,target_date)
    try:
        cursor.execute(sql)
    except Exception as e:
        db.rollback()
        print(str(e))
    else:
        db.commit()

# 目标日期股票
def target_is(target_date,type):
    sql = 'select stock_no as 股票代码,stock_name as 股票名称,price_max as 收盘价,target_date as 第7预计交易日期 from stock_7days where target_date="%s" order by stock_no ;' %(target_date)
    cursor.execute(sql)
    results = cursor.fetchall()
    stocks = []
    for stock in results:
        row =[]
        for info in stock:
            row.append(info)
        stocks.append(row)
    print(target_date,type,stocks)
    return stocks

# 更新7日目标日期最低价target_price、并且判断是否符合要求：最低价不破跳空缺口
def target_mysql(trade_date,stock_no,trade_date1,stock_no1,trade_date2,stock_no2):
    sql1= "update stock_7days set target_price=(select price_min from stock_tradeinfo_total where trade_date='%s' and stock_no='%s'),target_chg=(select price_chg from stock_tradeinfo_total where trade_date='%s' and stock_no='%s') " \
          "where target_date='%s' and stock_no='%s';"%(trade_date,stock_no,trade_date1,stock_no1,trade_date2,stock_no2)
    cursor.execute(sql1)
    db.commit()
    sql2= "update stock_7days set target_is='是' where target_price/price_max>1 and target_price/price_max<1.3 and target_chg>-8 and target_date='%s' and stock_no='%s';" %(trade_date,stock_no)
    cursor.execute(sql2)
    db.commit()


# 统计第8日开始的涨跌幅表现
def result_mysql(trade_date,stock_no,trade_date1,stock_no1):
    sql3= "update stock_7days set result_chg=concat_ws(',',result_chg,FORMAT((select price_chg from stock_tradeinfo_total where trade_date='%s' and stock_no='%s'),2)) where target_date='%s' and stock_no='%s';" %(trade_date,stock_no,trade_date1,stock_no1)
    cursor.execute(sql3)
    db.commit()
    #print(sql3)

#  收盘后统计昨天涨停股票：前一天最高价<当天最低价 price_max=前一天最高价
def get_stock(i):
        stocks = top_up(get_trade_day(i+1,'s'),'19.8','昨天温和放量涨停的股票：')
        for stock in stocks:
            stock_no = stock[1]
            price_max = stock[3]
            turnover = stock[5]*1.8
            info = top_next(get_trade_day(i,'s'),stock_no, price_max,turnover)
            if info:
                data_mysql(get_trade_day(i+1,'s'),info[1],info[2],price_max,'涨停温和放量1',get_trade_day(i-5))

# 收盘后统计今天涨停股票：当天最低价>前一天最高价 price_max=当天最低价
def get_stock2(i):
    stocks = top_up(get_trade_day(i,'s'), '19.8','今日2倍放量涨停的股票：')
    for stock in stocks:
        stock_no = stock[1]
        turnover = stock[5] * 0.6
        price_min = stock[6]
        info = top_last(get_trade_day(i + 1,'s'), stock_no, price_min, turnover)
        if info:
            data_mysql(get_trade_day(i,'s'), info[1], info[2], price_min, '涨停1.5倍放量2', get_trade_day(i - 5))

# 当天实时统计
def update_target(i):
    trade_date = get_trade_day(i,'s')
    stocks = target_is(trade_date,'7日目标价位买入：')
    for stock in stocks:
        stock_no = stock[0]
        target_mysql(trade_date, stock_no, trade_date, stock_no,trade_date, stock_no)

# 收盘后统计目标日前在3天内的数据
def update_result():
    for i in range(0,3):       # 3
        trade_date = get_trade_day(3-i,'s')
        stocks = target_is(trade_date,'7日目标价位后续涨跌幅：')
        for stock in stocks:
            stock_no = stock[0]
            result_date = get_trade_day(0,'s')
            result_mysql(result_date, stock_no, trade_date, stock_no)

# 存量数据
def update_exist_data(n):
    for i in range(1, n):
        update_target(i)


if __name__ == '__main__':
    #update_exist_data(20)
    #delete_mysql()
    #get_stock(7)           # 昨天涨停股票
    get_stock2(7)          # 今天涨停股票
    #update_target(0)       # 当天实时统计
    #update_result()        # 收盘后统计
